package com.flysky.common;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

/**
 * 
 * @author kp18166
 *data format
 *891165946|SCSP|GTPL-TKO|891165946|SCSP|3004|1|BID|31.5|ASK|PRICE|20131015|N|N|USD|RTSX|1|RT-EQ-DS|UTC-17|20131015
 *881890925|SCSP|GTPL-TKO|881890925|SCSP|2778|4.5|BID|||PRICE|20131016|N|N|GHS|XGHA|1|RT-EQ-DS|UTC-18|20131016
 *891231979|SCSP|GTPL-TKO|891231979|SCSP|2960|1.15|BID|||PRICE|20131016|N|N|BGN|XBUL|1|FTS|UTC-01|20131016
 */
public class ImportPmc {
	private String jdbc_driver = "oracle.jdbc.OracleDriver";
	private String jdbc_url = "jdbc:oracle:thin:@//cnbjlscd01.apac.nsroot.net:17206/cnctud";
	private String jdbc_user = "CGMPSPB";
	private String jdbc_pwd = "*******";
	private static int batchsize = 1000;
	
	public void importFormTxt(String filePath) {
		FileInputStream fis = null;
		InputStreamReader isr = null;
		BufferedReader br = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			Class.forName(jdbc_driver);
			conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pwd);
			String sql = "insert into pmc values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			
			String str = "";
			
			fis = new FileInputStream(filePath);// FileInputStream

			isr = new InputStreamReader(fis);

			br = new BufferedReader(isr);
			
			int rowNum = 0;
			int batchNo = 1;
			long tmpT1 = System.currentTimeMillis();
			System.out.println("import PMC start at:"+(new SimpleDateFormat("yyyy.MM.dd HH:mm:ss")).format(tmpT1));
			while ((str = br.readLine()) != null) {
				String[] rowData = str.split("\\|");
				
				if(rowData.length>=20){
					rowNum++;
					for(int i = 0; i < 20; i++){
						stmt.setString(i+1, rowData[i]);
					}
					stmt.addBatch();
				}
				
				if(rowNum == batchNo * batchsize){
					++batchNo;
					stmt.executeBatch();
					System.out.println("insert into "+rowNum+" success!");
					stmt.clearBatch();
				}
			}
			if ((batchNo - 1) * batchsize < rowNum) {
				stmt.executeBatch();
				System.out.println("insert into "+rowNum+" success!");
				stmt.clearBatch();
			}
			long tmpT2 = System.currentTimeMillis();
			System.out.println("import PMC end at:"+(new SimpleDateFormat("yyyy.MM.dd HH:mm:ss")).format(tmpT2));
			System.out.println("use time:"+(tmpT2-tmpT1)/1000+"s");
			
		} catch (FileNotFoundException e) {
			System.out.println("no file found");
		} catch (IOException e) {
			System.out.println("read file failure");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				br.close();
				isr.close();
				fis.close();
				stmt.close();
				conn.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void importHamper(String filePath){
		FileInputStream fis = null;
		InputStreamReader fir = null;
		BufferedReader br = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			Class.forName(jdbc_driver);
			conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pwd);
			String sql = "insert into hamper values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			
			fis = new FileInputStream(filePath);
			fir = new InputStreamReader(fis);
			br = new BufferedReader(fir);
			String str="";
			
			int rowNum = 0;
			int batchNo = 1;
			long tmpT1 = System.currentTimeMillis();
			System.out.println("import HAMPER start at:"+(new SimpleDateFormat("yyyy.MM.dd HH:mm:ss")).format(tmpT1));
			while((str = br.readLine())!=null){
				String[] rowData = str.split("\\|");
				//System.out.println("row length:"+rowData.length);
				if(null!=rowData && rowData.length>=16){
					rowNum++;
					for(int i = 1; i < 16; i++){
						//System.out.print("---"+rowData[i]);
						pstmt.setString(i, rowData[i]);
					}
					pstmt.addBatch();
				}
				
				if(batchsize*batchNo == rowNum){
					batchNo++;
					pstmt.executeBatch();
					pstmt.clearBatch();
					System.out.println("insert into HAMPER "+rowNum+" rows success!");
				}
			}
			if(batchsize*(batchNo-1) < rowNum){
				pstmt.executeBatch();
				pstmt.clearBatch();
				System.out.println("insert into HAMPER "+rowNum+" rows success!");
			}
			
			long tmpT2 = System.currentTimeMillis();
			System.out.println("import HAMPER end at:"+(new SimpleDateFormat("yyyy.MM.dd HH:mm:ss")).format(tmpT2));
			System.out.println("use time:"+(tmpT2-tmpT1)/1000+"s");
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				fis.close();
				fir.close();
				br.close();
				pstmt.close();
				conn.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void main(String[] args) {
		ImportPmc importPmc = new ImportPmc();
//		importPmc.importFormTxt("C:/PSP/PMC/sss.txt");
		importPmc.importHamper("C:/PSP/PMC/gtpl_ncl_base_late_20131017.txt");
	}

}
